package com.colorful.exceldemo.toolkit;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.colorful.exceldemo.annotation.ExcelSelected;
import com.colorful.exceldemo.toolkit.excel.CustomSheetWriteHandler;
import com.colorful.exceldemo.toolkit.excel.ExcelSelectedResolve;
import com.colorful.exceldemo.toolkit.excel.SourceMap;
import com.google.common.base.Charsets;
import org.springframework.stereotype.Component;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.net.URLEncoder;
import java.util.*;

/**
 * @author : shujuelin
 * @date : 16:18 2022/3/27
 */
@Component
public class EasyExcelUtil {
    public static final String FILE_SUFFIX_XLSX = ".xlsx";
    public static final String FILE_SUFFIX_XLS = ".xls";

    /**
     * 无合并单元格合并
     * @param file
     * @param clazz
     * @param headRowNumber
     * @param <T>
     * @return
     */
    public static <T> List<T> read(MultipartFile file, Class<T> clazz, Integer headRowNumber){
        UploadExcelListener<T> uploadExcelListener = new UploadExcelListener();
        String suffix = Objects.requireNonNull(file.getOriginalFilename()).substring(file.getOriginalFilename().lastIndexOf("."));
        if (!(FILE_SUFFIX_XLSX.equals(suffix) || FILE_SUFFIX_XLS.equals(suffix))) {
            throw new RuntimeException("表格文件格式不正确，请检查后重试");
        }
        try {
            InputStream inputStream = file.getInputStream();
            EasyExcel.read(inputStream)
                    .registerReadListener(uploadExcelListener)
                    .sheet()
                    .headRowNumber(headRowNumber)
                    .head(clazz)
                    .doReadSync();
            EasyExcel.read(file.getInputStream()).build();
//            EasyExcel.read(inputStream,clazz,uploadExcelListener)
//                    .headRowNumber(headRowNumber)
//                    .sheet()
//                    .headRowNumber(headRowNumber)
//                    .head(clazz)
//                    .doReadSync();
//            EasyExcel.read(file.getInputStream()).build();
        } catch (IOException e) {
            e.printStackTrace();
        }
        return uploadExcelListener.getData();
    }

    public static <T> void export(HttpServletResponse response, String fileName, String sheetName,  Class<T> clazz,List<T> dataList) {
        try {
            response.setContentType("application/vnd.ms-excel");
            response.setCharacterEncoding(Charsets.UTF_8.name());
            fileName = URLEncoder.encode(fileName, Charsets.UTF_8.name());
            response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
            ExcelWriter build = EasyExcel.write(response.getOutputStream()).build();
            WriteSheet writeSheet = writeSelectedSheet(clazz, 0, sheetName);
            //写
            build.write(new ArrayList<String>(), writeSheet);
            //手动关闭
            build.finish();
        } catch (Exception  e) {
            e.printStackTrace();
        }
    }
    /**
     * 创建即将导出的sheet页（sheet页中含有带下拉框的列）
     * @param head 导出的表头信息和配置
     * @param sheetNo sheet索引
     * @param sheetName sheet名称
     * @return sheet页
     */
    public static  <T> WriteSheet writeSelectedSheet(Class<T> head, Integer sheetNo, String sheetName) {
        LinkedHashMap<Integer, ExcelSelectedResolve> selectedMap = resolveSelectedAnnotation(head);

        return EasyExcel.writerSheet(sheetNo, sheetName)
                .head(head)
//                .registerWriteHandler(new CommentWriteHandler()) //创建批注
//                .registerWriteHandler(new CustomColumnWidthStyle()) //自适应宽度
                //绑定下拉数据源
                .registerWriteHandler(new CustomSheetWriteHandler(selectedMap))
                .build();
    }
    /**
     * 解析表头类中的下拉注解
     * @param <T> 泛型
     * @param head 表头类
     * @return Map<下拉框列索引, 下拉框内容> map
     */
    private static <T> LinkedHashMap<Integer, ExcelSelectedResolve> resolveSelectedAnnotation(Class<T> head) {
        //用来存储对应的列与值对象
        LinkedHashMap<Integer, ExcelSelectedResolve> selectedMap = new LinkedHashMap<>();

        // getDeclaredFields(): 返回全部声明的属性；getFields(): 返回public类型的属性
        Field[] fields = head.getDeclaredFields();
        for (int i = 0; i < fields.length; i++){
            Field field = fields[i];
            // 解析注解信息
            ExcelSelected selected = field.getAnnotation(ExcelSelected.class);
            ExcelProperty property = field.getAnnotation(ExcelProperty.class);
            if (selected != null) {
                ExcelSelectedResolve excelSelectedResolve = new ExcelSelectedResolve();
                Map<String, List<String>> stringListMap = excelSelectedResolve.resolveSelectedSource(selected);
                SourceMap sourceMap = new SourceMap();
                if (stringListMap != null) {
                    stringListMap.forEach((k, v) -> {
                        if (v != null) {
                            sourceMap.setMap(stringListMap);
                        }
                    });
                }
                excelSelectedResolve.setSourceMap(sourceMap);
                String[] source = convertArray(stringListMap);
                if (source.length > 0){
                    excelSelectedResolve.setRef(selected.ref());
                    if (excelSelectedResolve.getRef() == 0) {
                        excelSelectedResolve.setSource(source);
                    }
                    excelSelectedResolve.setFirstRow(selected.firstRow());
                    excelSelectedResolve.setLastRow(selected.lastRow());
                    if (property != null && property.index() >= 0){
                        selectedMap.put(property.index(), excelSelectedResolve);
                    } else {
                        selectedMap.put(i, excelSelectedResolve);
                    }
                }
            }
        }

        return selectedMap;
    }


    /**
     * 字符串数组转换成map
     * @param strings
     * @return
     */
    public static Map<String, List<String>> convertMap(String[] strings) {
        Map<String, List<String>> map = new LinkedHashMap<>();
        for (String string : strings) {
            map.put(string, null);
        }
        return map;
    }

    /**
     * map转数组
     * @param map
     * @return
     */
    public static String[] convertArray(Map<String, List<String>> map) {
        if (map != null) {
            String[] strings = new String[map.size()];
            List<String> collect = new ArrayList<>(map.keySet());
            return collect.toArray(strings);
        }
        return null;
    }
    /**
     * 字符转数字
     * @param colStr
     * @param length
     * @return
     */
    public static int excelColStrToNum(String colStr, int length) {
        int num = 0;
        int result = 0;
        for(int i = 0; i < length; i++) {
            char ch = colStr.charAt(length - i - 1);
            num = (int)(ch - 'A' + 1) ;
            num *= Math.pow(26, i);
            result += num;
        }
        return result;
    }

    /**
     * Excel column index begin 1
     * @param columnIndex
     * @return
     */
    public static String excelColIndexToStr(int columnIndex) {
        if (columnIndex <= 0) {
            return null;
        }
        String columnStr = "";
        columnIndex--;
        do {
            if (columnStr.length() > 0) {
                columnIndex--;
            }
            columnStr = ((char) (columnIndex % 26 + (int) 'A')) + columnStr;
            columnIndex = (int) ((columnIndex - columnIndex % 26) / 26);
        } while (columnIndex > 0);
        return columnStr;
    }
}
